package com.ydd.app.dao.impl;

import com.ydd.common.util.CommonUtil;


public class NewsDaoSql {
	
	/**
	 * 查询新闻分类列表
	 */
	
	public final static String QUERY_NEWS_TYPES_SQL = 
				" SELECT "
			    +"  news_type_id , " 
			    +"  type_name "
			    +" FROM "
			    +"  news_type_mst "
			    +" where "
			    +"  is_delete = '0' "
			    +"  order by news_type_id asc ";
	
	/**
	 * 查询新闻列表(后台)
	 */
	public static String queryNewsInBackgroundSql(final int page, final int pageCount,
			final String newsTypeId,final String newsStatusId) {
		final StringBuilder sb = new StringBuilder();
		sb.append(" SELECT " +
				  "    top  "+pageCount
	              +" newsd.news_id,  "
	              +" newsd.create_time, "
	              +" newsd.title, " 
	              +" newsd.news_type_id, "
	              +" news.type_name, " 
	              +" newsd.update_time, "
	              +" newsd.picture_url,  "
	      		  +" newsd.news_describe,  "
	              +" newsd.news_status  "
	              +" FROM  "
	              +" news_detail_mst as newsd " 
	              +" inner join news_type_mst as news on news.news_type_id = newsd.news_type_id "
	              +" where " 
	              +"  news.is_delete = '0' " 
	              +"  and newsd.is_delete = '0' ");
		if( 0 != page ) {
			sb.append("   and newsd.news_id not in ( ");
			sb.append(" select " 
					  +"    top  "+ (pageCount * page)
					  +" newsd.news_id  "
		              +" FROM  "
		              +" news_detail_mst as newsd " 
		              +" inner join news_type_mst as news on news.news_type_id = newsd.news_type_id "
		              +" where " 
		              +"  news.is_delete = '0' " 
		              +"  and newsd.is_delete = '0' ");
			if( CommonUtil.isNotEmpty(newsStatusId)) {
				sb.append(" and newsd.news_status = " +newsStatusId);
			}
			if(CommonUtil.isNotEmpty(newsTypeId)) {
				sb.append(" and news.news_type_id = "+newsTypeId );
			}
			sb.append(" order by newsd.update_time desc )" );
		}
		if( CommonUtil.isNotEmpty(newsStatusId)) {
			sb.append(" and newsd.news_status = " +newsStatusId);
		}
		if(CommonUtil.isNotEmpty(newsTypeId)) {
			sb.append(" and news.news_type_id = "+newsTypeId );
		}
		sb.append(" order by newsd.update_time desc " );
		return sb.toString();
	}
	
	/**
	 * 查询新闻列表(后台)
	 */
	public static String queryNewsInBackgroundInWeb(final int page, final int pageCount,final String newsTypeId, final String keyWord) {
		final StringBuilder sb = new StringBuilder();
		sb.append(" SELECT " +
				  "    top  "+pageCount
	              +" newsd.news_id,  "
	              +" newsd.title, " 
	  		      +" newsd.create_time, "
	              +" newsd.news_type_id, "
	              +" news.type_name, " 
	      	      +" picture_url,  "
	    	      +" news_describe,  "
	              +" newsd.update_time, "
	              +" newsd.news_status  "
	              +" FROM  "
	              +" news_detail_mst as newsd " 
	              +" inner join news_type_mst as news on news.news_type_id = newsd.news_type_id "
	              +" where " 
	              +"  news.is_delete = '0'"
	              +" and news_status = '1' " );
	              if(null != keyWord && "" != keyWord){
	            	  sb.append(" and newsd.title like '%"+ keyWord +"%' ");
	              }
	              
	              sb.append("  and newsd.is_delete = '0' ");
		if( 0 != page ) {
			sb.append("   and newsd.news_id not in ( ");
			sb.append(" select " 
					  +"    top  "+ (pageCount * page)
					  +" newsd.news_id  "
		              +" FROM  "
		              +" news_detail_mst as newsd " 
		              +" inner join news_type_mst as news on news.news_type_id = newsd.news_type_id "
		              +" where " 
		              +"  news.is_delete = '0' " 
		              +"  and newsd.is_delete = '0' "
		              +" and news_status = '1' " );
		              if( CommonUtil.isNotEmpty(newsTypeId)) {
		            	  sb.append(" and news.news_type_id =  "+newsTypeId  );
		              }
		              sb.append(" order by newsd.update_time desc ) ");

		}
	    if( CommonUtil.isNotEmpty(newsTypeId)) {
      	  sb.append(" and news.news_type_id =  "+newsTypeId  );
        }
		sb.append(  " order by newsd.update_time desc " );
		return sb.toString();
	}
	
	/**
	 * 查询新闻列表(后台)
	 */
	public static String queryNewsInBackgroundWhenNewest(final int pageCount) {
		final StringBuilder sb = new StringBuilder();
		sb.append(" SELECT " +
				  "    top  "+pageCount
	              +" newsd.news_id,  "
	              +" newsd.title, " 
	  		      +" newsd.create_time," 
	  		      +"  SUBSTRING(newsd.news_detail,0,100) news_detail,  "
	              +" newsd.news_type_id, "
	              +" news.type_name, " 
	              +" newsd.update_time, "
	              +" newsd.picture_url,  "
	      		  +" newsd.news_describe,  "
	              +" newsd.news_status  "
	              +" FROM  "
	              +" news_detail_mst as newsd " 
	              +" inner join news_type_mst as news on news.news_type_id = newsd.news_type_id "
	              +" where " 
	              +"  news.is_delete = '0'"
	              +" and news_status = '1' " 
	              +"  and newsd.is_delete = '0' ");
		return sb.toString();
	}
	
	/**
	 * 查询新闻列表
	 */
	public final static String QUERY_NEWS_SQL = 
			" SELECT "
            +" newsd.news_id, "
		    +" newsd.create_time,  "
            +" newsd.title," 
            +" newsd.news_type_id,"
            +" news.type_name," 
            +" newsd.update_time  "
            +" FROM  "
            +" news_detail_mst as newsd" 
            +" inner join news_type_mst as news on news.news_type_id = newsd.news_type_id "
            +" where" 
            +"  newsd。is_delete = '0'" 
            +"  and news.is_delete = '0' " 
            +" and news_status = '1' "
            +" order by update_time desc"
            ;
	
	/**
	 * 查询新闻详细
	 */
	public final static String QUERY_NEWS_DETAIL_SQL = 
			" SELECT "
		    +" newsd.news_id, "
		    +" newsd.user_id, "
		    +" newsd.title, "
		    +" newsd.news_detail, "
		    +" newsd.create_time, "
		    +" newsd.update_time, "
		    +" newsd.news_type_id,"
		    +" news.type_name," 
		    +" newsd.news_status," 
		    +" newsd.review_reason," 
		    +" newsd.review_time," 
		    +" newsd.review_id," 
		    +"news_describe," 
		    +"picture_url  "
		    +" FROM  "
		    +" news_detail_mst as newsd" 
		    +" inner join news_type_mst as news on news.news_type_id = newsd.news_type_id "
		    +" where" 
		    +"  newsd.is_delete = '0' "
		    +"  and news.is_delete = '0' " 
		    +" and newsd.news_id = ? "
            ;
	
	/**
	 * 创建新闻
	 */
	public final static String INSERT_NEWS_SQL = 
			" insert "
            +" into  "
            +" news_detail_mst "
            +" ( "
            +" title, "
            +" news_status, "
            +" news_detail, "
            +" create_time, "
            +" update_time, "
            +" is_delete, "
            +" review_time, "
            +" user_id, "
            +" news_type_id," 
            +" news_describe," 
            +" picture_url "
            +" ) "
            +" values "
            +" (?,'0',?,getdate(),getdate(),'0',getdate(),?,?,?,?) ";

	/**
	 * 更新新闻
	 */
	public final static String UPDATE_NEWS_SQL = 
			" update "
            +" news_detail_mst "
            +" set "
            +" title = ? , "
            +" news_detail = ? , "
            +" news_status = '0' , "
            +" update_time = getdate() , "
            +" news_type_id = ? ," 
            +"  news_describe = ? ," 
            +" picture_url = ?"
            +" where "
            +" news_id = ?  "
            +" and is_delete = '0'  ";
	
	/**
	 * 删除新闻
	 */
	public final static String DELETE_NEWS_SQL = 
			" update "
            +" news_detail_mst "
            +" set  "
            +" is_delete = '1' "
            +" where "
            +" news_id = ?  "
            +" and is_delete = '0' ";
	
	/**
	 * 审核新闻
	 */
	public final static String REWIEW_NEWS_SQL = 
			" update "
            +" news_detail_mst "
            +" set  "
            +" news_status = ? , "
            +" review_reason = ? , "
            +" review_time = getdate(), "
            +" review_id = ?  "
            +" where  "
            +" news_id = ?  "
            +" and is_delete = '0' ";
	
	/**
	 * 查询行业列表
	 */
	public final static String QUERY_HANGYETPYE_SQL = 
			" SELECT "
            +" Id, "
            +"  Name "
            +" FROM  "
            +" HangYeType "
            +" order by Id desc "
            ;
	
	/**
	 * 客户注册
	 */
	public final static String INSERT_CUSTOMER_SQL = 
			" insert into customer_mst (member_type,customer_name,customer_nickname,password) values(?,?,?,?) ";

}
